﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data.SqlClient;
using System.Data;
using BookManage.Model;
namespace BookManage.DAL
{
    public class ReaderTypeDAL
    {
        public static int Add(ReaderType readertype)
        {
            int rows = 0;
            string sql = "insert into TB_ReaderType(rdType,rdTypeName,CanLendQty,CanLendDay,CanContinueTimes,PunishRate,DateValid)"
                + " values(@rdType,@rdTypeName,@CanLendQty,@CanLendDay,@CanContinueTimes,@PunishRate,@DateValid)";
            SqlParameter[] parameters =
            {
                new SqlParameter("@rdType",readertype.rdType),
                new SqlParameter("@rdTypeName",readertype.rdTypeName),
                new SqlParameter("@CanLendQty",readertype.CanLendQty),
                new SqlParameter("@CanLendDay",readertype.CanLendDay),
                new SqlParameter("@CanContinueTimes",readertype.CanContinueTimes),
                new SqlParameter("@PunishRate",readertype.PunishRate),
                new SqlParameter("@DateValid",readertype.DateValid)
            };
            try
            {
                rows = SqlHelper.ExecuteNonQuery(sql, parameters);
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return rows;
        } //添加

        public static int Delete(ReaderType readertype)
        {
            int rows = 0;
            string sql = "delete from TB_ReaderType where rdType=@rdType";
            SqlParameter[] parameters = { new SqlParameter("@rdType", readertype.rdType) };
            try
            {
                rows = SqlHelper.ExecuteNonQuery(sql, parameters);
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return rows;
        } //删除
        public static int Update(ReaderType readertype)
        {
            int rows = 0;
            string sql = "update TB_ReaderType set "
                //+"rdType=@rdType,"
                + "rdTypeName=@rdTypeName,"
                + "CanLendQty=@CanLendQty,"
                + "CanLendDay=@CanLendDay,"
                + "CanContinueTimes=@CanContinueTimes,"
                + "PunishRate=@PunishRate,"
                + "DateValid=@DateValid "
                + "where rdType=@rdType";
            SqlParameter[] parameters =
            {
                new SqlParameter("@rdType",readertype.rdType),
                new SqlParameter("@rdTypeName",readertype.rdTypeName),
                new SqlParameter("@CanLendQty",readertype.CanLendQty),
                new SqlParameter("@CanLendDay",readertype.CanLendDay),
                new SqlParameter("@CanContinueTimes",readertype.CanContinueTimes),
                new SqlParameter("@PunishRate",readertype.PunishRate),
                new SqlParameter("@DateValid",readertype.DateValid)
            };
            try
            {
                rows = SqlHelper.ExecuteNonQuery(sql, parameters);
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return rows;
        } //更新
        /// <summary>
        /// 由读者类型ID(rdType)得到该读者类型信息，返回DataRow
        /// </summary>
        public static DataRow GetDRByID(int rdType)  //根据关键字（即读者类型）获得读者类型
        {
            string sql = "select * from TB_ReaderType where rdType=@rdType";
            SqlParameter[] parameters = { new SqlParameter("@rdType", rdType) };
            DataTable dt = null;
            dt = SqlHelper.GetDataTable(sql, parameters, "TB_ReaderType");
            DataRow dr = null;
            if (dt == null || dt.Rows.Count == 0)
                return dr;
            else
            {
                dr = dt.Rows[0];
                return dr;
            }
        }
        public static ReaderType GetObjectByID(int rdType)
        {
            DataRow dr = GetDRByID(rdType);
            return SqlHelper.DataRowToT<ReaderType>(dr);
        }

        public static DataTable GetAll()
        {
            string sql = "select * from TB_ReaderType";
            return SqlHelper.GetDataTable(sql, null, "TB_ReaderType");
        }  //获取ReaderType表中全部数据

        public static DataTable GetReaderType(int rdType, string rdTypeName) 
        {
            string sql;
            if (rdType <= 0 && rdTypeName == "")
            {
                sql = "select * from TB_ReaderType where 0=1";
                return SqlHelper.GetDataTable(sql, null, "TB_ReaderType");
            }

            rdTypeName = (rdTypeName == "") ? ("%") : ("%" + rdTypeName + "%");
            if (rdType <= 0)
            {
                sql = "select * from TB_ReaderType where rdTypeName like @rdTypeName";
                SqlParameter[] parameters =
                {
                    new SqlParameter("@rdTypeName",rdTypeName)
                };
                return SqlHelper.GetDataTable(sql, parameters, "TB_ReaderType");
            }
            else
            {
                sql = "select * from TB_ReaderType where rdType=@rdType and rdTypeName like @rdTypeName";
                SqlParameter[] parameters =
                {
                    new SqlParameter("@rdType",rdType),
                    new SqlParameter("@rdTypeName",rdTypeName)
                };
                return SqlHelper.GetDataTable(sql, parameters, "TB_ReaderType");
            }
        }  //通过类别号和类别名获取数据库中对应的数据
    }
}
